USE SoftUni
GO

--IF OBJECT_ID('dbo.tbl_PerformanceComparison', 'U') IS NOT NULL -- The 'U' for the second param apparently means "Only look for objects with this name that are tables".
--BEGIN
--	DROP TABLE tbl_PerformanceComparison
--END
--GO

IF OBJECT_ID('dbo.tbl_PerformanceComparison', 'U') IS NULL -- The 'U' for the second param apparently means "Only look for objects with this name that are tables".
BEGIN
	CREATE TABLE tbl_PerformanceComparison
	(
		performanceComparison_sampleTime DATETIME NOT NULL,
		performanceComparison_sampleText NVARCHAR(MAX) NOT NULL
	)

	DECLARE @i INT = 1
	DECLARE @sampleTime DATETIME = GETDATE()
	DECLARE @sampleText NVARCHAR(MAX)

	WHILE @i <= 500000 -- 500 000
	BEGIN
		SET @sampleTime = DATEADD(hh, -1, @sampleTime) -- add '-1' hour
		SET @sampleText = 'Sample text - ' + CAST(@i AS NVARCHAR(MAX))

		INSERT INTO tbl_PerformanceComparison
			VALUES (@sampleTime, @sampleText)

		SET @i = @i + 1
	END
END
GO

SELECT COUNT(performanceComparison_sampleText)
	FROM tbl_PerformanceComparison
GO

--IF EXISTS(SELECT * FROM sys.indexes WHERE object_id = object_id('dbo.tbl_PerformanceComparison') AND NAME ='index_C_performanceComparison_sampleDate')
--BEGIN
--    DROP INDEX index_C_performanceComparison_sampleDate ON dbo.tbl_PerformanceComparison
--END
--GO

-- Cleaning cache
DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS
GO

--SELECT *
--	FROM tbl_PerformanceComparison
--	WHERE YEAR(performanceComparison_sampleTime) BETWEEN YEAR('2014/01/01') AND YEAR('2015/12/31') 
--GO

--CREATE UNIQUE CLUSTERED INDEX index_C_performanceComparison_sampleDate ON tbl_PerformanceComparison(performanceComparison_sampleTime)
--GO

SELECT *
	FROM tbl_PerformanceComparison WITH (INDEX(index_C_performanceComparison_sampleDate))
	WHERE YEAR(performanceComparison_sampleTime) BETWEEN YEAR('2014/01/01') AND YEAR('2015/12/31')
GO